package com.hellogood.yaohuo.server.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hellogood.yaohuo.server.db.DBConnection;
import com.hellogood.yaohuo.server.db.DBHelper;
import com.hellogood.yaohuo.shared.entity.Org;
import com.hellogood.yaohuo.shared.entity.Quyu;
import com.hellogood.yaohuo.shared.entity.QuyuOrg;
import com.hellogood.yaohuo.shared.exception.DatabaseException;

public class QuyuOrgDAO extends DBHelper {

	@Override
	public List<?> initializeObject(ResultSet rs) throws SQLException {
		List<QuyuOrg> list = new ArrayList<QuyuOrg>();
		if (rs != null) {
			while (rs.next()) {
				QuyuOrg qyOrg = new QuyuOrg();
				qyOrg.setOrgCode(rs.getString("OrgCode"));
				qyOrg.setQyCode(rs.getString("QyCode"));
				qyOrg.setOrgName(rs.getString("OrgName"));
				list.add(qyOrg);
			}
		}
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<QuyuOrg> getQuyuOrg() {
		String hxDbName = DBConnection.hxDbName;
		String sql = "SELECT QyCode,OrgCode,(SELECT OrgName FROM " + hxDbName + "..SubShop WHERE OrgCode = q.OrgCode) AS OrgName FROM QyOrg q";
		List<?> list = super.query(sql);
		return (List<QuyuOrg>) list;
	}

	public List<QuyuOrg> getQuyuOrgByQuyuCode(String quyuCode) {
		String hxDbName = DBConnection.hxDbName;
		String sql = "SELECT QyCode,OrgCode,(SELECT OrgName FROM " + hxDbName + "..SubShop WHERE OrgCode = q.OrgCode) AS OrgName FROM QyOrg q WHERE QyCode = '" + quyuCode + "'";
		List<?> list = super.query(sql);
		return (List<QuyuOrg>) list;
	}

	public String addOrgToQuyu(Quyu qy, Org org) {
		String sql = "INSERT INTO QyOrg(QyCode,OrgCode) VALUES('" + qy.getQyCode() + "','" + org.getOrgCode() + "')";
		try {
			super.execute(sql);
		} catch (DatabaseException e) {
			e.printStackTrace();
			return e.getMessage();
		}
		return "";
	}

	public String removeOrgFromQuyu(Org org) {
		String sql = "DELETE FROM QyOrg WHERE OrgCode = '" + org.getOrgCode() + "'";
		try {
			super.execute(sql);
		} catch (DatabaseException e) {
			e.printStackTrace();
			return e.getMessage();
		}
		return "";
	}

	public String removeOrgFromQuyu(String quyuCode) {
		String sql = "DELETE FROM QyOrg WHERE QyCode = '" + quyuCode + "'";
		try {
			super.execute(sql);
		} catch (DatabaseException e) {
			e.printStackTrace();
			return e.getMessage();
		}
		return "";
	}

	public String addOrgToQuyu(List<Org> orgs, String qyCode) {
		List<String> sqls = new ArrayList<String>();
		if (orgs != null && !orgs.isEmpty()) {
			String sql = "";
			for (Org org : orgs) {
				sql = "INSERT INTO QyOrg(QyCode,OrgCode) VALUES('" + qyCode + "','" + org.getOrgCode() + "')";
				sqls.add(sql);
			}
			try {
				super.executeBatch(sqls);
			} catch (DatabaseException e) {
				e.printStackTrace();
				return e.getMessage();
			}
		}
		return "";
	}

	public String addOrgCodeToQuyu(List<String> codes, String qyCode) {
		List<String> sqls = new ArrayList<String>();
		if (codes != null && !codes.isEmpty()) {
			String sql = "";
			for (String code : codes) {
				sql = "INSERT INTO QyOrg(QyCode,OrgCode) VALUES('" + qyCode + "','" + code + "')";
				sqls.add(sql);
			}
			try {
				super.executeBatch(sqls);
			} catch (DatabaseException e) {
				e.printStackTrace();
				return e.getMessage();
			}
		}
		return "";
	}

	public String removeOrgFromQuyu(List<String> orgCodeList) {
		List<String> sqls = new ArrayList<String>();
		if (orgCodeList != null && !orgCodeList.isEmpty()) {
			for (String code : orgCodeList) {
				String sql = "DELETE FROM QyOrg WHERE OrgCode = '" + code + "'";
				sqls.add(sql);
			}
			try {
				super.executeBatch(sqls);
			} catch (DatabaseException e) {
				e.printStackTrace();
				return e.getMessage();
			}
		}
		return "";
	}

	public List<QuyuOrg> getQuyuOrgByCode(String code) {
		String hxDbName = DBConnection.hxDbName;
		// String sql = "SELECT * FROM QyOrg q," + hxDbName +
		// "..SubShop o WHERE o.orgCode = q.orgCode AND q.qyCode = '" + code +
		// "' or q.orgCode = '" + code + "'";
		String sql = "select s.orgcode,s.orgname,(select qycode from qyorg where orgcode = s.orgcode) as qycode from " + hxDbName
				+ "..SubShop s where orgCode in (select orgcode from qyorg q where q.qyCode = '" + code + "' or q.orgCode = '" + code + "')";
		List<?> list = super.query(sql);
		return (List<QuyuOrg>) list;
	}
}
